package assem;

import java.sql.ResultSet;
import java.util.HashMap;
import java.util.Vector;
import java.util.TreeMap;
import java.util.TreeSet;

import util.Debug.ErrorReport;

import assem.enums.*;
import assem.Utils;
import jpave.annotator.CoreMain;
import jpave.database.OverviewDBWrapper;

// Detect and upgrade schema versions

/** 
 * to change schema number: 
 * (1) change in util.methods.Version
 * (2) change in this file: add to DBVer emum, add to Schema constructor,
 * change method current(), and change in the schema.
 ***/

/**
 * Max length of protein or RNA identifier to 30
 * its hardcoded in the tables. If it gets changed, change in DoUniProt.java
**/

/***
* CHAR: tinytext 1 byte, text 2 byte, mediumtext 2 byte
* max	256 char		65k char		16M char
* 		use VARCHAR for fields that need to be searched
* INT	tinyint 1 byte, smallint 2 byte,  mediumint 3 byte, int 4 byte, bigint 8 byte
* max	256               65k                16M				4394M		
* 		float 4 byte,  double 8 byte
*****/

enum DBVer 
{
	Ver10, // 1.0 - the original, perl assembler version
	Ver15, // 1.5 - this is version 1.0, partially upgraded by SNPMain. Not usable for assembly.
	// doSNP.pl Ver20 is upgraded to Ver22 for annotation and viewing
	Ver20, // 2.0 - the first Java assembler version 
	Ver22, // 2.2 - originally 2.0 or 1.5, partially upgraded by the annotator and viewer. Not usable for assembly.	
	Ver25, // 2.5 - the full, 2.5 schema for assembly
	//Ver26, // 2.6 - upgraded from 2.5
	Ver30, // 3.0 - added a few annotation tables 
	Ver35, // 3.5 - a few columns 
	Ver40, // 4.0 - few columns for reps
	Ver41; // 4.1 - few columns for annoDB
}

/********************************************
 * Dynamic fields: L__<lib> for counts, LN__<lib> for RPKM
 * assem_msg.peptide if protein databases
 * assem_msg.hasLoc if has location information (Library.java)
 */
public class Schema 
{
	DBConn mDB;
	DBVer dbVer = DBVer.Ver10; // default to this, as in other cases we get it from the schemver table
	String dbVerStr = null;
	DBVer curVer = DBVer.Ver41;
	String curVerStr = "4.1";
	
	public Schema(DBConn db)
	{
		mDB = db;		
		try
		{
			ResultSet rs = mDB.executeQuery("select schemver from schemver");
			rs.first();
			dbVerStr = rs.getString("schemver");
			if (dbVerStr.equals("2.0")) 		dbVer = DBVer.Ver20;
			else if (dbVerStr.equals("1.5"))	dbVer = DBVer.Ver15;
			else if (dbVerStr.equals("2.2"))	dbVer = DBVer.Ver22;
			else if (dbVerStr.equals("2.5"))	dbVer = DBVer.Ver25;
			else if (dbVerStr.equals("3.0"))	dbVer = DBVer.Ver30;
			else if (dbVerStr.equals("3.5"))	dbVer = DBVer.Ver35;
			else if (dbVerStr.equals("4.0"))	dbVer = DBVer.Ver40;
			else if (dbVerStr.equals("4.1"))	dbVer = DBVer.Ver41;
			else 
				System.err.println("Unknown version string " + dbVerStr + 
					" in schemver table; default to 1.0");
		}
		catch (Exception e){}
	}
	/*******************************************************************
	 * SCHEMA
	 */
	public void load40Schema() throws Exception
	{
		ResultSet rs = mDB.executeQuery("show tables");
		if (rs.first())
		{
			Log.die("Cannot create database: tables already exist!");	
		}
		String sql = "" +
			"create table library " +
			"	( " +
			"	LID integer PRIMARY KEY AUTO_INCREMENT, " +
			"	libid varchar(30) NOT NULL, " +
			"	 fastafile varchar(250) NOT NULL, " +
			"	 fastqfile varchar(250) NOT NULL, " +
			"	 fiveprimesuf varchar(10) NOT NULL,  " +
			"	 threeprimesuf varchar(10) NOT NULL,  " +
			"	 title tinytext,  " +
			"	 organism tinytext, " +
			"	 cultivar tinytext, " +
			"	 strain tinytext, " +
			"	 tissue tinytext, " +
			"	 stage tinytext,  " +
			"	 treatment tinytext,  " +
			"	 year tinytext,  " +
			"	 source tinytext, " +
			"	 sourcelink tinytext, " +
			"	 loaddate timestamp NOT NULL, " +
			"	 expdate datetime, " +
			"	 exploaded boolean default 0, " + /* these are also added in LoadLibMain.fixExpCols */
			"	 defqual tinytext, " +
			"	 libsize int NOT NULL, " +
			"	 avglen int default 0, " +
			"     medlen int default 0, " +
			"     ctglib boolean default 0, " +
			"    prefix tinytext, " +
			"	orig_libid varchar(30), " +
			"   parent varchar(30), " +
			"   reps TEXT," +
			"	 UNIQUE INDEX unq1(libid) " +
			"	) " +
			"	ENGINE=MyISAM; ";
		mDB.executeUpdate(sql);
		
		// NOTE: mis-named table. It is not a clone, its a read/EST
		sql = "" +
			"create table clone " +
			"	( " +
			"	CID bigint NOT NULL PRIMARY KEY AUTO_INCREMENT, " +
			"	cloneid varchar(30) NOT NULL, " +
			"	 origid varchar(30) NOT NULL, UNIQUE INDEX (origid),  " +
			"	 libid varchar(30) NOT NULL, " +
			"	 sequence MEDIUMTEXT NOT NULL, " + 
			"	 quality MEDIUMTEXT NOT NULL, " +  
			"	 source smallint UNSIGNED default 0, " +
			"	 sense tinyint NOT NULL, " +
			"	 length int NOT NULL, " +
			"     mate_CID bigint NOT NULL, " +
			"	LID integer NOT NULL, " +
			"	chimeric boolean not null default 0, " +
			" " +
			"	UNIQUE INDEX unq1(cloneid), /* we should remove this restriction!! needs jpave fix */ " +
			"	INDEX lid1(LID, cloneid), " +
			"	INDEX lid2(LID,CID) " +
			" " +
			"	) " +
			"	ENGINE=MyISAM; ";
		mDB.executeUpdate(sql);	
		sql = "" +
			"create table clone_exp " +
			"	( " +
			"	CID bigint NOT NULL , " +
			"	LID integer NOT NULL, " +
			"	count integer NOT NULL, " +
			"	rep smallint unsigned default 0, " +
			"	UNIQUE INDEX unq1(CID,LID,rep) " +
			" " +
			"	) " +
			"	ENGINE=MyISAM; ";
		mDB.executeUpdate(sql);			
		sql = "" +
			"create table assembly  " +
			"	( " +
			"	AID integer NOT NULL PRIMARY KEY, " + // CAS 6/10/15 remove auto_increment
			"	assemblyid varchar(30) NOT NULL, " +
			"	 username varchar(30),  " +
			"	 descr tinytext, " +
			"	 assemblydate date NOT NULL, " +
			"	 projectpath tinytext, " +
			"	 annotationdate date, " +
			"	completed tinyint NOT NULL, " +
			"	completedate date, " +
			"	erate float default 0, " +
			"	exrate float default 0, " +
			"	ppx tinyint default 0, " + // used to be N value for 1EN for normalizing expression levels
			" " +
			"	UNIQUE INDEX unq1(assemblyid) " +
			"	) " +
			"	ENGINE=MyISAM; " ;
		mDB.executeUpdate(sql);
		sql = "" +
			"create table ASM_tc_iter " +
			"( " +
			"	TCID integer PRIMARY KEY AUTO_INCREMENT, " +
			"	AID integer NOT NULL, " +
			"	tctype tinytext NOT NULL, " +
			"	tcnum integer, " +
			"	finished tinyint default 0, " +
			"	clustiter_done smallint default 0, " +
			"	ctgs_start integer default 0, " +
			"	ctgs_end integer default 0, " +
			"	merges_tried integer default 0, " +
			"	merges_ok integer default 0 " +
			" " +
			") " +
			"ENGINE=INNODB; " ;
		mDB.executeUpdate(sql);
		sql = "" +
			"create table assemlib " +
			"	( " +
			"	AID integer NOT NULL, " +
			"	LID integer NOT NULL, " +
			"	assemblyid varchar(30) NOT NULL, " +
			"	 libid varchar(30) NOT NULL, " +
			"	singletons int default 0, " + 
			"   contigs int default 0,  " +
			"   uniqueContigs int default 0, " +
			"	goreads bigint default 0, " +
			"   UNIQUE INDEX (assemblyid,LID)," +
			"   UNIQUE INDEX (AID,LID)" +
			"	) " +
			"	ENGINE=MyISAM; ";
		mDB.executeUpdate(sql);
		
		// clusterid is no longer used but accessed in many places - remove later
		sql = "" +
			"create table contig " +
			"	( " +
			"	CTGID bigint NOT NULL PRIMARY KEY AUTO_INCREMENT, " +
			"	AID integer NOT NULL, " +
			"	TCID integer default 0, " +  // for assembly
			"	SCTGID bigint default 0, " + // for assembly
			"	contigid varchar(30) NOT NULL, " +
			"	 assemblyid varchar(30) NOT NULL, " +
			"	 clusterid varchar(30) , " +
			"	 numclones INT NOT NULL, " +
			/* CAS 6/8/15 for sequence files with locations
			   database has to be created from scratch since new feature */
			"   seq_group VARCHAR(30), " +   // e.g. scaffold_1
			"   seq_start bigint default 0, " + 
			"   seq_end  bigint  default 0, " +
			"   seq_strand tinytext, " +
			"	 consensus MEDIUMTEXT NOT NULL, " + // holds 2**24
			"	 orig_ccs MEDIUMtext, " +  /* this gives a way to fix the contigs, in case doSNP has a bug and messes them up */
			"	 quality MEDIUMTEXT NOT NULL, " +
			"	 consensus_bases INT, " +
			"	 frpairs int NOT NULL, " +
			"	 has_ns BOOLEAN default 0, " +
			"	 nstart integer default 0, " +
			"	 est_5_prime SMALLINT, " +
			"	 est_3_prime SMALLINT, " +
			"	 est_loners SMALLINT, " +
			//"	 est_chimeric TINYINT default 0,  " +
			"	  " +
			"	 rstat FLOAT default 0, " +
			"	 recap BOOLEAN default 0,  " +
			"	 longest_clone VARCHAR(30), " +
			"	 notes VARCHAR(255), " +
			"	 mate_CTGID bigint default 0, " +
			"	 rftype tinytext, " +
			"	 buried_placed boolean default 0, " +
			"	avg_pct_aligned smallint default 0, " +
			"	finalized boolean default 0, " +
			"   totalexp bigint unsigned default 0," +
			"   totalexpN bigint unsigned default 0," +
			"	 " +
			"	INDEX idx1(contigid), " +
			"	INDEX idx2(assemblyid), " +
			"	INDEX idx3(assemblyid,contigid), " +
			"	INDEX idx4(tcid), " +
			"	INDEX idx5(sctgid) " +
			"	) " +
			"	ENGINE=MyISAM; ";
		mDB.executeUpdate(sql);
		sql = "" +
			"create table contclone " +
			"	( " +
			"	CTGID bigint NOT NULL,  " +
			"	CID bigint NOT NULL, " +
			"	contigid varchar(30) NOT NULL, " +
			"	cloneid varchar(30) NOT NULL, " +
			"	orient char(1) NOT NULL, " +
			"	leftpos integer NOT NULL, " +
			"	gaps MEDIUMTEXT, " +
			"	extras MEDIUMTEXT, " +
			"	ngaps integer default 0, " +
			"   mismatch mediumint default 0, " + 
			"   numex mediumint default 0, " + 
			"	buried TINYINT default 0, " +
			"	prev_parent bigint default 0, " +
			"	pct_aligned smallint default 0,  " +
			"	 INDEX idx1(contigid,cloneid), " +
			"	 PRIMARY KEY prim(CTGID,CID) " +
			" " +
			"	) " +
			"	ENGINE=MyISAM; ";
		mDB.executeUpdate(sql);
		// WN if you change buryclone you need to change reBuryClones in SNPMain.
		sql = "" +
			"create table buryclone " +
			"	( " +
			"	AID integer NOT NULL, " +
			"	CID_child bigint NOT NULL, INDEX(CID_child), " +
			"	CID_parent bigint NOT NULL, INDEX(CID_parent), " +
			"	CID_topparent bigint default 0, INDEX(CID_topparent), " +
			"	assemblyid varchar(30) NOT NULL,   " +
			"	 childid varchar(30) NOT NULL, INDEX(childid),   " +
			"	 parentid varchar(30) NOT NULL, INDEX(parentid),  " +
			"	bcode tinytext,	" +		/* -- either cap or blast */
			"	flipped tinyint(0), " +
			"	 PRIMARY KEY prim(AID, CID_child), " +
			"	 INDEX idx1(assemblyid, childid), " +
			"	 TCID integer default 0 " + /*	-- to permit rollback of TC stages  */
			" " +
			"	) " +
			"	ENGINE=MyISAM; ";
		mDB.executeUpdate(sql);
		sql = "" +
			"create table ASM_cliques " +
			"( " +
			"	AID integer NOT NULL, " +
			"	CQID bigint PRIMARY KEY AUTO_INCREMENT, " +
			"	type tinytext, " +
			"	nclone integer default 0, " +
			"	assembled tinyint default 0, " +
			"	cap_success tinyint default 0 " +
			") " +
			"ENGINE=INNODB; ";
		mDB.executeUpdate(sql);
		sql = "" +
			"create table ASM_clique_clone " +
			"( " +
			"	CQID bigint, " +
			"	CID bigint, " +
			"	PRIMARY KEY(CQID,CID), " +
			" " +
			"	FOREIGN KEY (CQID) REFERENCES ASM_cliques(CQID) ON DELETE CASCADE  " +
			") " +
			"ENGINE=INNODB; ";
		mDB.executeUpdate(sql);
			/* ASM_scontig stands for "supercontig" 
			it holds either one or two actual assembled contigs 
			purpose is to track contig pairs through the assembly 
			it is these which are merged during the TC rounds  */
		sql = "" +
			"create table ASM_scontig " +
			"( " +
			"	SCID bigint PRIMARY KEY AUTO_INCREMENT, " +
			"	AID integer NOT NULL, " +
			"	CTID1 bigint not null, " +
			"	CTID2 bigint default 0, " +
			"	TCID integer not null, " +
			"	merged_to integer default 0, " +
			"	capbury_done tinyint default 0, " +
			" " +
			"	UNIQUE INDEX unq1(CTID1,CTID2), " +
			"	INDEX idx1(TCID), " +
			"	FOREIGN KEY (TCID) REFERENCES ASM_tc_iter(TCID) ON DELETE CASCADE " +
			") " +
			"ENGINE=INNODB; ";
		mDB.executeUpdate(sql);
		sql = "" +
			"create table ASM_tc_edge " +
			"( " +
			"	EID bigint PRIMARY KEY AUTO_INCREMENT, " +
			"	TCID integer, " +
			"	SCID1 bigint, " +
			"	SCID2 bigint, " +
			"	score integer, " +
			"	CLUSTID bigint default 0, " +
			"	attempted tinyint default 0, " +
			"	succeeded tinyint default 0, " +
			"	errstr tinytext, " +
			"	errinfo tinytext, " +
			"	SCID_result bigint default 0, " +
			"	EID_from bigint default 0, " +
			"	olap smallint default 0, " +
			"	identity smallint default 0, " +
			"	hang smallint default 0, " +
			" " +
			"	UNIQUE INDEX unq1(TCID,SCID1,SCID2), " +
			"	INDEX scodx(TCID,score), " +
			"	INDEX att(TCID,attempted), " +
			"	 FOREIGN KEY (TCID) REFERENCES ASM_tc_iter(TCID) ON DELETE CASCADE , " +
			"	 FOREIGN KEY (SCID1) REFERENCES ASM_scontig(SCID) ON DELETE CASCADE , " +
			"	 FOREIGN KEY (SCID2) REFERENCES ASM_scontig(SCID) ON DELETE CASCADE  " +
			") " +
			"ENGINE=INNODB; ";
		mDB.executeUpdate(sql);
		sql = "" +
			"create table contig_counts " +
			"	(contigid varchar(30) NOT NULL, " +
			"	 libid varchar(30) NOT NULL, " +
			"	 count int NOT NULL, " +
			" " +
			"	 PRIMARY KEY (contigid, libid) " +
			"	) ENGINE=MyISAM; ";
		mDB.executeUpdate(sql);
		
		// fields for annotatePAVE, though snp and indel are now done during assembly
		sql = "" +
			"ALTER TABLE contig " +
			"	ADD orient char(1), " +
			"	ADD gc_ratio FLOAT, " +
			" " +
			/* SNP statistics  */
			"	ADD snp_count SMALLINT, " +
			"	ADD indel_count SMALLINT, " +
			"	ADD allele_count SMALLINT, " +
			
			"	ADD e_frame TINYINT default null, " +
			"	ADD e_evidence TINYTEXT, " +
			" " +
			/* Largest ORF coding information  */
			"	ADD o_frame 		TINYINT default null, " +
			"	ADD o_coding_start 	MEDIUMINT unsigned default null, " +
			"	ADD o_coding_end   	MEDIUMINT unsigned default null, " +
			"	ADD o_coding_has_begin 	BOOLEAN, " +
			"	ADD o_coding_has_end 	BOOLEAN, " +
			" " +
			/* UniProt hits   */
			"	ADD p_frame 		TINYINT   default null, " +
			"	ADD p_coding_start  MEDIUMINT unsigned default null, " + // not used
			"	ADD p_coding_end 	MEDIUMINT unsigned default null, " + // not used
			"	ADD p_coding_has_begin BOOLEAN, " +   // used to indicate protein frame was used for ORF selection
			"	ADD p_coding_has_end 	BOOLEAN, " + // not used
		
			// db2.6 change bestscore to float, removed defaults of 0
			"   ADD PID bigint, " +  			// was added in load25schemaAnnotate
			"	ADD bestmatchid VARCHAR(30), " + // need for 1st best hit counts
			"   ADD PIDov bigint, " +  			
			"	ADD INDEX idx6(AID,bestmatchid), " +   // for updating description
			"   ADD cnt_swiss	  smallint  unsigned default 0, " +
			"   ADD cnt_trembl    smallint  unsigned default 0, " +
			"   ADD cnt_nt        smallint unsigned  default 0, " +
			"   ADD cnt_gi        smallint unsigned  default 0, " + // CAS 2/6/15
			"   ADD cnt_overlap   SMALLINT  unsigned default 0, " +
			"   ADD cnt_species   SMALLINT  unsigned default 0, " +
			"   ADD cnt_gene	  SMALLINT  unsigned default 0, " +
			"   ADD cnt_taxo	  SMALLINT  unsigned default 0, " +
			"   ADD cnt_pairwise  smallint  unsigned default 0";
		mDB.executeUpdate(sql);
		
		sql = "" +
			"create table ASM_assemtime " +
			"( " +
			"	AID integer not null, " +
			"	stage tinytext, " +
			"	time_start datetime " +
			") " +
			"ENGINE=MyISAM; ";
		mDB.executeUpdate(sql);

		sql = "" +
			"create table ASM_params " +
			"( " +
			"	AID integer not null, " +
			"	pname tinytext, " +
			"	pvalue text " +
			" " +
			") " +
			"ENGINE=MyISAM; " ;
		mDB.executeUpdate(sql);
		sql = "" +
			"create table ASM_cmdlist " +
			"( " +
			"	AID integer not null, " +
			"	descr VARCHAR(100), " +
			"	cmdstr text, " +
			"	 " +
			"	UNIQUE INDEX unq(AID,descr) " +
			") " +
			"ENGINE=MyISAM; " ;
		mDB.executeUpdate(sql);
		sql = "" +
			"create table snp " +
			"( " +
			"	SNPID bigint not null PRIMARY KEY AUTO_INCREMENT, " +
			"	CTGID bigint not null, " +
			"	pos integer not null, " +
			"	basevars tinytext, " +
			"	annot text, " +
			"	numvars smallint, " +
			"	snptype tinytext, " +
			"	score float default 0, " +
			" " +
			"	INDEX(CTGID) " +
			") " +
			"ENGINE=MyISAM; " ;
		mDB.executeUpdate(sql);
		sql = "" +
			"create table snp_clone " +
			"( " +
			"	SNPID bigint not null, " +
			"	CID bigint not null, " +
			"	snptype tinytext, " +
			" " +
			"	UNIQUE INDEX(SNPID, CID), " +
			"	INDEX(CID) " +
			" " +
			") " +
			"ENGINE=MyISAM; " ;
		mDB.executeUpdate(sql);
		
			/* this table exists to hold the lengthy assembly summary " +
				message, so selects from assembly are less cluttered 
			 */ 
		sql = "" +
			"create table assem_msg " +
			"	( " +
			"	AID integer NOT NULL PRIMARY KEY, " +
			"	msg text, " +
			"   pja_msg text default null" +
			"	) " +
			"	ENGINE=MyISAM; ";
		mDB.executeUpdate(sql);
		sql = "" +
			"create table schemver " +
			"( " +
			"	schemver tinytext " +
			") " +
			"ENGINE=MyISAM; ";
		mDB.executeUpdate(sql);	
		sql = "insert into schemver (schemver) values('" + currentVerString() + "'); ";
		mDB.executeUpdate(sql);	
		
		loadSchemaAnnotate();

		addRstat();
	}
	/* 
	 * XXX The tables specific to annotation
	*/
	public void loadSchemaAnnotate () throws Exception
	{
		String sql;
		if (!mDB.hasTable("pja_db_unitrans_hits")) { // v2.5 as pja_uniprot_hits
			sql = "" + 
			"create table pja_db_unitrans_hits " +
			"	(PID 			bigint not null PRIMARY KEY AUTO_INCREMENT, " +
			"	 CTGID 			bigint not null, " +
			"	 AID 			integer not null, " +     // v3.0
			"	 DUHID 			integer not null, " +	  // v3.0 unique hit index
			"	 contigid 		varchar(30) NOT NULL, " +
			"	 uniprot_id 	varchar(30) NOT NULL, " +  // hitID  ...
			"	 dbtype			varchar(10), " + 
			"	 taxonomy 		varchar (20)," +			 
			// blast fields
			" 	 percent_id		SMALLINT  unsigned, " +
			"	 alignment_len	INTEGER  unsigned, " +		
			"	 mismatches		SMALLINT  unsigned, " +
			"	 gap_open		SMALLINT  unsigned, " +
			"	 ctg_start		MEDIUMINT unsigned, " +
			"	 ctg_end		MEDIUMINT unsigned, " +
			"	 prot_start 	MEDIUMINT unsigned, " +
			"	 prot_end   	MEDIUMINT unsigned, " +
			"	 e_value 		DOUBLE PRECISION, " +
			"	 bit_score 		float, " +
			// computed fields 
			"	 blast_rank 	int  unsigned default 0, " +
			"	 isProtein 		boolean default 0, " +			
			"    filtered		tinyint  unsigned default 0, " +		
			"	 filter_best  	boolean default 0, " +               // bestEval
			"	 filter_ovbest	boolean default 0, " +           // bestAnno
			"	 filter_olap	boolean default 0, " +
			"	 filter_top3	boolean default 0, " +
			"	 filter_species	boolean default 0, " +
			"	 filter_gene	boolean default 0, " +
			" 	 cnt_hsps		 smallint unsigned default 0, " +			  " " +
			//"	UNIQUE INDEX unq1(CTGID, uniprot_id), " + // CAS 2/21/15 -- just makes it crash -- how get dups?
			" INDEX(filter_best), " +
			" INDEX(filter_ovbest), " +
			" INDEX(filter_olap), " +
			" INDEX(filter_top3), " +
			" INDEX(filter_species), " +
			" INDEX(filter_gene), " +
			" INDEX(uniprot_id), " +
			" INDEX(contigid), " +
			" INDEX(DUHID), " +  // CAS 2/21/15
			" INDEX(CTGID) ";
		
			sql += "	) ENGINE=MyISAM; ";
			mDB.executeUpdate(sql);
		}
		
		if (!mDB.hasTable("pja_pairwise")) { // v2.5
			sql = "create table pja_pairwise " +
			"(" +
			"PWID 		integer not null PRIMARY KEY AUTO_INCREMENT, " +
			"AID 		integer not null, " +
			"contig1 	varchar(30) NOT NULL, " +
			"contig2 	varchar(30) NOT NULL, " +
			" " +
			"coding_frame1 TINYINT, " +
			"coding_frame2 TINYINT, " +
			" " +
			"NT_hsr_ratio	Float default 0, " + 
			"NT_hsr_score	int  unsigned default 0, " +   
			"NT_hsr_len		int  unsigned default 0, " +			
			"NT_olp_ratio	Float default 0, " +
			"NT_olp_score	int  unsigned default 0, " +
			"NT_olp_len		int  unsigned default 0, " +
			"NT_largest_gap int  unsigned default 0, " +
			" " +			
			"AA_hsr_ratio	Float default 0, " +
			"AA_hsr_score	int  unsigned default 0, " +
			"AA_hsr_len		int  unsigned default 0, " +
			"AA_olp_ratio	Float default 0, " +
			"AA_olp_score	int  unsigned default 0, " +
			"AA_olp_len		int  unsigned default 0, " +
			"AA_largest_gap int  unsigned default 0, " +
			" " +		
			"in_self_blast_set 		BOOLEAN default 0, " +
			"in_uniprot_set 			BOOLEAN default 0, " +
			"in_translated_self_blast BOOLEAN default 0, " +
			// added v3.0 if have protein results
			" shared_hitID			varchar(30), " +
			" cnt_shared_hits		SMALLINT  unsigned default 0, " +
			// added v3.0 if have selfblast result
			" e_value 		DOUBLE PRECISION, " +
			" percent_id				SMALLINT  unsigned default 0, " +
			" alignment_len			INTEGER  unsigned default 0, " +
			" cnt_hsps				SMALLINT  unsigned default 0, " +
			" ctg1_start				SMALLINT default 0, " +
			" ctg1_end				SMALLINT default 0, " +
			" ctg2_start 			SMALLINT default 0, " +
			" ctg2_end				SMALLINT default 0, " +
			"assembly_id varchar(30) " + // remove
			" " +
			") " +
			"ENGINE=MyISAM; ";
			mDB.executeUpdate(sql);
		}
		
		if (!mDB.hasTable("pja_databases")) { // v3.0
			sql = "create table pja_databases " +
			"(" +
			"DBID 		integer not null PRIMARY KEY AUTO_INCREMENT, " +
			"AID 		integer not null, " +
			"path 		varchar(250), " +
			"dbtype 		varchar(10), " + // tr, sp, nt, Ref90....
			"taxonomy 	varchar(20), " + // plants, invert, vert,...
			"isProtein 	boolean, " +
			"nOnlyDB		integer  unsigned default 0,"  +   // v4.1 #contigs with hit only to this db CAS 2/2/15
			"nBestHits integer unsigned default 0, " +     // #contigs best Eval
			"nOVBestHits integer  unsigned default 0, " +  // #contigs best Anno
			"nTotalHits 	integer  unsigned default 0, " +   // #contigs with hit
			"nUniqueHits integer  unsigned default 0, " +  // #proteins/nt unique
			"nHighPercent integer unsigned default 0, " +
			"nLowPercent integer unsigned default 0, " +
			"dbDate 		date, " +
			"addDate 	date, " +
			"subset		boolean default false, " + 
			"parameters text,  " +							// v4.1
			"INDEX(DBID) ";
			sql += "	) ENGINE=MyISAM; ";
		
			mDB.executeUpdate(sql);
		}
		
		if (!mDB.hasTable("pja_db_unique_hits")) { // v3.0
			sql = "create table pja_db_unique_hits " +
			"(" +
			"DUHID 			integer not null PRIMARY KEY AUTO_INCREMENT, " +
			"AID 			integer not null, " +
			"DBID 			integer not null, " +  	// database this came from
			"hitID 			varchar (30), " +     // UniProtID or ntID
			"repID			varchar (30), " +     // second id in Uniprot - not used			
			"dbtype 			varchar (10), " +
			"taxonomy 		varchar(20), " + 		
			"isProtein 		boolean default 0, " +
			"nUnitranHit    smallint unsigned default 0, " +  			
			"nBestHit       smallint unsigned  default 0, " +  	
			"description 	varchar(250), " +
			"species 		varchar (100), " +
			"length			integer default 0, " +  
			"sequence 		MEDIUMTEXT, " +
			"kegg	 		MEDIUMTEXT, " +
			"pfam 			MEDIUMTEXT, " +
			"ec 			MEDIUMTEXT, " +
			"INDEX(hitID), index(nBestHit) ";
			sql += "	) ENGINE=MyISAM; ";
			mDB.executeUpdate(sql);
		}
		
		if (!mDB.hasTable("pja_db_species")) { // v3.0
			sql = "create table pja_db_species " +
			"(" +
			"AID 			bigint not null, " +
			"species 		varchar (100), " +     		
			"count    		int unsigned default 0, " +  // total count
			"nBestHits     int unsigned default 0, " +   // best eval  CAS 2/2/15
			"nOVBestHits     int unsigned default 0, " + // best anno
			" index(AID,species) )";
			mDB.executeUpdate(sql);
		}
	}
	
	void addRstat() throws Exception
	{
		ResultSet rs = mDB.executeQuery("show tables like 'rstat_thresh'");
		if (!rs.first())
		{
			String sql = "" +
				"create table rstat_thresh " +
				"( " +
				"	assemblyid varchar(30), " +
				"	libid1 varchar(30), " +
				"	libid2 varchar(30), " +
				"	rstat int not null, " +
				"	rstype tinytext, " +
				"	simcount float not null, " +
				"	obscount int not null, " +
				"	conf float not null, " +
				"	INDEX(assemblyid, libid1, libid2) " +
				") " +
				"ENGINE=MyISAM; " ;
			mDB.executeUpdate(sql);		
		}
	}
	/***************************************************************
	 * XXX Version methods
	 */
	// Update the following two, and add to third method
	public static String currentVerString() {return "4.1";}
	public static DBVer currentVer() { return DBVer.Ver41;}
	
	public boolean current()
	{
		return (dbVer == currentVer());	
	}
	public boolean pre30()
	{
		return (dbVer == DBVer.Ver25 || dbVer == DBVer.Ver22 || dbVer == DBVer.Ver20 
				|| dbVer == DBVer.Ver15 || dbVer == DBVer.Ver10);
	}
	
	public void update() throws Exception
	{
		if (current()) return;
		if (needsPrompt())
		{
			if (!Utils.yesNo("The database needs to be updated to the current format; continue?"))
			{
				System.err.println("Terminated");
				System.exit(0);
			}
		}
		System.err.println("Checking database schema version");
		switch(dbVer)
		{
			case Ver10: case Ver15: case Ver20: case Ver22: case Ver25:
				System.err.println("Database version " + dbVer);
				System.err.println("Sorry, your database is too old to be used with TCW.");
				System.err.println("Recreate is recommended.");
				System.exit(0);
				break;
			case Ver30:
				updateTo35();
				updateTo40();
				break;
			case Ver35:
				updateTo40();
				break;
			case Ver40:
				updateTo41();
				break;
			default: ;
		}
		System.err.println("   Check schema complete");
	}
	
	boolean needsPrompt()
	{
		return (dbVer != DBVer.Ver40 && dbVer != DBVer.Ver35);
	}
	void updateTo40() throws Exception
	{
		if (!mDB.tableHasColumn("library", "reps"))
		{
			mDB.executeUpdate("alter table library add reps TEXT after parent");
		}
		if (!mDB.tableHasColumn("clone_exp", "rep"))
		{
			mDB.executeUpdate("alter table clone_exp add rep smallint unsigned default 0 after count");
			// Must add the new index before dropping old due to foreign keys
			mDB.executeUpdate("alter table clone_exp add index unq2(CID,LID,rep)");
			mDB.executeUpdate("alter table clone_exp drop index unq1");
		}
		mDB.executeUpdate("update library set reps='' where reps is null");
		
		if (!mDB.tableHasColumn("schemver", "annoVer")) {
			mDB.executeUpdate("alter table schemver add annoVer tinytext");
			mDB.executeUpdate("alter table schemver add annoDate tinytext");
		}	
		if (mDB.tableExists("pja_db_unique_hits"))
		{
			if (!mDB.tableHasColumn("pja_db_unique_hits", "kegg"))
			{
				mDB.executeUpdate("alter table pja_db_unique_hits add kegg mediumtext");
			}
			if (!mDB.tableHasColumn("pja_db_unique_hits", "pfam"))
			{
				mDB.executeUpdate("alter table pja_db_unique_hits add pfam mediumtext");
			}
			if (!mDB.tableHasColumn("pja_db_unique_hits", "ec"))
			{
				mDB.executeUpdate("alter table pja_db_unique_hits add ec mediumtext");
			}
		}
			
		mDB.executeUpdate("update schemver set schemver='4.0'");
	}
	/***********************************************************
	 * Feb 2015 
	 */
	void updateTo41() throws Exception
	{
		System.err.println("\nUpdating columns for Feb15 4.1 release"); 
     	mDB.executeUpdate("update assem_msg set pja_msg = NULL where AID = 1");
     	System.err.println("   Delete species table to regenerate");
        mDB.executeUpdate("delete from pja_db_species");
        updateTo41giColumns();
        
        ResultSet rs = mDB.executeQuery("show columns from pja_databases where field='parameters'");
		if (!rs.next()) 
			mDB.executeUpdate("alter table pja_databases add parameters text");
		
		rs = mDB.executeQuery("show columns from pja_databases where field='nHighPercent'");
		if (!rs.next()) {
			mDB.executeUpdate("alter table pja_databases add nHighPercent int unsigned default 0");
			mDB.executeUpdate("alter table pja_databases add nLowPercent  int unsigned default 0");
		}

		rs = mDB.executeQuery("show columns from pja_databases where field='nOnlyDB'");
		if (!rs.first()) {
			mDB.executeUpdate("alter table pja_databases add nOnlyDB int unsigned default 0");
			mDB.executeUpdate("alter table pja_db_species add nBestHits  int unsigned default 0");
		}
        mDB.executeUpdate("update schemver set schemver='4.1'");
         
		OverviewDBWrapper ov = new OverviewDBWrapper(mDB.getDBconn());
		ov.createOverview(new Vector <String> ());
		
		return;
	}
	
	// This has nothing to do with Overview, but added in here for Feb15 release
	private void updateTo41giColumns() {
		try {
			ResultSet rs = mDB.executeQuery("show columns from contig where field='cnt_gi'");
			if (rs.next()) return;
			
			CoreMain.PrtSpMsg(1, "Add GI count column and populate");
			mDB.executeUpdate("alter table contig add cnt_gi int unsigned default 0");
			rs = mDB.executeQuery("select count(*) from pja_db_unitrans_hits where dbtype='gi'");
			rs.next();
			int cnt = rs.getInt(1);
			if (cnt==0) return;
			
			rs = mDB.executeQuery("select count(*) from contig where cnt_gi>0");
			rs.next();
			cnt = rs.getInt(1);
			if (cnt==0) return; // updated already
			
			CoreMain.PrtSpMsg(1, "Database has NCBI hits, updating #NCBI column for sequence.");
			HashMap <Integer, Integer> map = new HashMap <Integer, Integer> ();
			rs = mDB.executeQuery("select CTGID from pja_db_unitrans_hits where dbtype='gi'");
			while (rs.next()) {
				int ctg = rs.getInt(1);
				if (map.containsKey(ctg)) map.put(ctg, map.get(ctg)+1);
				else map.put(ctg, 1);
			}
			cnt=0;
			for (int ctgid : map.keySet()) {
				mDB.executeUpdate("update contig set cnt_gi=" + map.get(ctgid) + " where CTGID=" +  ctgid);
				cnt++;
			}
			CoreMain.PrtSpMsg(2, "Update " + cnt + " sequences");
			
			rs = mDB.executeQuery("select count(*) from pja_db_unitrans_hits " +
					"where dbtype!='gi' and dbtype!='sp' and dbtype!='tr'");
			rs.next();
			cnt = rs.getInt(1);
			if (cnt==0) return;
			
			CoreMain.PrtSpMsg(1, "Database has nt or other hits, updating #Nucleotide column for sequence.");
			mDB.executeUpdate("update contig set cnt_nt=0");
			map.clear();
			rs = mDB.executeQuery("select CTGID from pja_db_unitrans_hits " +
					"where dbtype!='gi' and dbtype!='sp' and dbtype!='tr'");
			while (rs.next()) {
				int ctg = rs.getInt(1);
				if (map.containsKey(ctg)) map.put(ctg, map.get(ctg)+1);
				else map.put(ctg, 1);
			}
			cnt=0;
			for (int ctgid : map.keySet()) {
				mDB.executeUpdate("update contig set cnt_nt=" + map.get(ctgid) + " where CTGID=" +  ctgid);
				cnt++;
			}
			System.err.println("   Update " + cnt + " sequences");
		}
		catch (Exception e) {ErrorReport.prtReport(e, "Cannot add GI column");}
	}
	// Added here if not in v3.0 or (not in v2.5 and not in updateSchemaAnnotate())
	void updateTo35() throws Exception
	{
		boolean skip=false;
		System.err.println("If your database is annotated and will not be annotated again, say yes to the following:");
		if (Utils.yesNo("Skip annotation changes")) skip=true;
		System.err.print("\nStarting update... \r");
		// These are needed coming from v2.5
		if (!mDB.tableHasColumn("contig", "e_evidence"))
		{
			mDB.checkAddColumn("contig", "e_evidence", "tinytext","");
			mDB.checkChangeColumn("contig", "consensus_bases", "int");
			mDB.checkChangeColumn("pja_db_unitrans_hits", "alignment_len", "int");
		}
		mDB.checkAddColumn("library", "prefix", "tinytext", "ctglib");
		mDB.checkAddColumn("library", "orig_libid", "varchar(30)", "prefix");
		mDB.checkAddColumn("library", "parent", "varchar(30)", "orig_libid");
		System.err.print("Checked/updated 6.... \r");
		
		mDB.checkAddColumn("library", "strain", "tinytext", "cultivar");
		mDB.checkDropColumn("library", "is_454");
		mDB.checkDropColumn("library", "in_genbank");
		System.err.print("Checked/updated 9.... \r");
		
		mDB.checkAddColumn("assemlib", "goreads", "bigint default 0", "uniqueContigs");
		
		mDB.checkAddColumn("contig", "PIDov", "bigint", "bestmatchid");
		mDB.checkAddColumn("contig", "cnt_taxo", "smallint default 0", "cnt_gene");
	    // if the database already exists and annotated, this is not necessary and change takes REALLY long
		
		if (!skip) {
			System.err.print("Change column #1 ... \r");
			mDB.checkChangeColumn("contig", "o_coding_start", "mediumint(8) unsigned");
			mDB.checkChangeColumn("contig", "o_coding_end", "mediumint(8) unsigned");
			mDB.checkChangeColumn("contig", "p_coding_start", "mediumint(8) unsigned");
			System.err.print("Change column #4... \r");
			mDB.checkChangeColumn("contig", "p_coding_end", "mediumint(8) unsigned");
			mDB.checkChangeColumn("contig", "PID", "bigint(20)");
			mDB.checkChangeColumn("pja_db_unitrans_hits", "ctg_start", "mediumint(8) unsigned");
			mDB.checkChangeColumn("pja_db_unitrans_hits", "ctg_end", "mediumint(8) unsigned");
			System.err.print("Change column #8... \r");
			mDB.checkChangeColumn("pja_db_unitrans_hits", "prot_start", "mediumint(8) unsigned");
			mDB.checkChangeColumn("pja_db_unitrans_hits", "prot_end", "mediumint(8) unsigned");
		}
		System.err.print("Update continuing......      \r");
		mDB.checkAddColumn("pja_db_unique_hits", "length", "integer default 0","");
		mDB.checkDropColumn("pja_db_unique_hits", "complement");		
		mDB.checkAddColumn("pja_databases", "subset", "boolean default false","addDate");
		mDB.checkAddColumn("pja_databases", "nBestHits", "integer unsigned default 0","isProtein");

		mDB.executeUpdate("update schemver set schemver='3.5'");
		dbVer = DBVer.Ver35;
		
		// The updateTo30 routine does RPKM but the released 3.0 does not, so they
		// may need to have RPKM done, if they want.
		Vector<Integer> aids = new Vector<Integer>();
		ResultSet rs = mDB.executeQuery("select aid,ppx from assembly");
		while (rs.next())
		{
			aids.add(rs.getInt("aid"));
		}
		if (aids.size() > 0)
		{

			System.err.println("Updating normalized values to RPKM.\nThis takes awhile, please be patient.....");
			for (int aid : aids)
			{
				Vector<String> libs = new Vector<String>();
				rs = mDB.executeQuery("select libid from assemlib where AID=" + aid);
				while (rs.next())
				{
					libs.add(rs.getString("libid"));
				}
				for (String libid : libs)
				{
					System.err.println("Updating " + libid + " ....\r");
					Utils.setRPKM(mDB, libid);
				}
				mDB.executeUpdate("update assembly set ppx=0 where aid=" + aid);
			}	
		}
		System.err.println("Complete update                        \n");
	}
	void updateTo30() throws Exception
	{
		// this is for doing snps with a p-value score 
		ResultSet rs = mDB.executeQuery("show columns from snp where field='score'");
		if (!rs.first())
		{
			mDB.executeUpdate("alter table snp add score float default 0");	
		}

		// this is for storing the estimate error and extras rate
		rs = mDB.executeQuery("show columns from assembly where field='erate'");
		if (!rs.first())
		{
			mDB.executeUpdate("alter table assembly add erate float default 0");	
			mDB.executeUpdate("alter table assembly add exrate float default 0");	
		}
		if (!mDB.tableHasColumn("assembly", "ppx"))
		{
			mDB.executeUpdate("alter table assembly add ppx tinyint default 0");	
		}

		// this eases the estimation of error rate
		rs = mDB.executeQuery("show columns from contclone where field='mismatch'");
		if (!rs.first())
		{
			mDB.executeUpdate("alter table contclone add mismatch mediumint default 0");
		}		
		// this eases the estimation of extras rate
		rs = mDB.executeQuery("show columns from contclone where field='numex'");
		if (!rs.first())
		{
			mDB.executeUpdate("alter table contclone add numex mediumint default 0");	
		}		
		
		// this allows buries to be restored after snp unburying
		rs = mDB.executeQuery("show columns from contclone where field='prev_parent'");
		if (!rs.first())
		{
			mDB.executeUpdate("alter table contclone add prev_parent bigint default 0");	
		}		
		
		// legacy column
		rs = mDB.executeQuery("show columns from clone where field='source'");
		if (!rs.first())
		{
			mDB.executeUpdate("ALTER TABLE clone ADD source BIGINT UNSIGNED default 0 AFTER quality;");	
		}				
		rs.close();		
		if (!mDB.hasTable("ASM_params"))
			mDB.executeUpdate("rename table params to ASM_params");

		if (!mDB.hasTable("clone_exp"))
		{
			String sql = "" +
			"create table clone_exp " +
			"	( " +
			"	CID bigint NOT NULL , " +
			"	LID integer NOT NULL, " +
			"	count integer NOT NULL, " +
			"	rep smallint unsigned default 0, " +
			"	UNIQUE INDEX unq1(CID,LID,rep) " +
			" " +
			"	) " +
			"	ENGINE=MyISAM; ";
			mDB.executeUpdate(sql);			
		}
		if (!mDB.tableHasColumn("library", "ctglib"))
		{
			mDB.executeUpdate("alter table library add ctglib boolean default 0");	
		}
		if (!mDB.tableHasColumn("library", "exploaded"))
		{
			mDB.executeUpdate("alter table library add expdate datetime");	
			mDB.executeUpdate("alter table library add exploaded boolean default 0");	
		}
		if (!mDB.tableHasColumn("assemlib", "singletons"))
		{	
			mDB.executeUpdate("alter table assemlib add singletons int default 0");	
			mDB.executeUpdate("alter table assemlib add contigs int default 0");	
			mDB.executeUpdate("alter table assemlib add uniqueContigs int default 0");	
		}
		addCtgLibFields(mDB);
		fillCtgLibFields(mDB);
		
		updateSchemaAnnotate();
		mDB.executeUpdate("update schemver set schemver='3.0'");
		dbVer = DBVer.Ver30;
	}	
	
	public void updateFrom20to22() throws Exception
	{
		// Small additions to the 2.0 database, that we can do on the fly
		// without a new DB version
		
		// this is for doing snps with a p-value score 
		ResultSet rs = mDB.executeQuery("show columns from snp where field='score'");
		if (!rs.first())
		{
			mDB.executeUpdate("alter table snp add score float default 0");	
		}

		// this is for storing the estimate error and extras rate
		rs = mDB.executeQuery("show columns from assembly where field='erate'");
		if (!rs.first())
		{
			mDB.executeUpdate("alter table assembly add erate float default 0");	
			mDB.executeUpdate("alter table assembly add exrate float default 0");	
		}

		// this eases the estimation of error rate
		rs = mDB.executeQuery("show columns from contclone where field='mismatch'");
		if (!rs.first())
		{
			mDB.executeUpdate("alter table contclone add mismatch mediumint default 0");
		}		
		// this eases the estimation of extras rate
		rs = mDB.executeQuery("show columns from contclone where field='numex'");
		if (!rs.first())
		{
			mDB.executeUpdate("alter table contclone add numex mediumint default 0");	
		}		
		
		// this allows buries to be restored after snp unburying
		rs = mDB.executeQuery("show columns from contclone where field='prev_parent'");
		if (!rs.first())
		{
			mDB.executeUpdate("alter table contclone add prev_parent bigint default 0");	
		}		
		
		// legacy column
		rs = mDB.executeQuery("show columns from clone where field='source'");
		if (!rs.first())
		{
			mDB.executeUpdate("ALTER TABLE clone ADD source BIGINT UNSIGNED default 0 AFTER quality;");	
		}				
		rs.close();
		mDB.executeUpdate("update schemver set schemver='2.2'");
	}
	public void updateFrom10to22() throws Exception
	{
		mDB.foreignKeysOff();
		
		// First add all the new  keys
		Utils.singleLineMsg("library table index...");
		mDB.writeLockTable("library");
		mDB.executeUpdate("alter table library add LID integer unique key auto_increment");

		Utils.singleLineMsg("assembly table index...");
		mDB.writeLockTable("assembly");
		mDB.executeUpdate("alter table assembly add AID integer unique key auto_increment");

		Utils.singleLineMsg("clone table index...");
		mDB.writeLockTable("clone");
		mDB.executeUpdate("alter table clone add CID bigint unique key auto_increment");
		
		Utils.singleLineMsg("contig table index...");
		mDB.writeLockTable("contig");
		mDB.executeUpdate("alter table contig add CTGID bigint unique key auto_increment not null");
		
		mDB.executeUpdate("delete from pairwise_contigs");
		
		Utils.singleLineMsg("add new tables...");
		String sql = "" +
		"create table ASM_tc_iter " +
		"( " +
			"TCID integer PRIMARY KEY AUTO_INCREMENT, " +
			" AID integer NOT NULL, " +
			"tctype tinytext NOT NULL, " +
			"tcnum integer, " +
			"finished tinyint default 0, " +
			"clustiter_done smallint default 0, " +
			"ctgs_start integer default 0, " +
			"ctgs_end integer default 0, " +
			"merges_tried integer default 0, " +
			"merges_ok integer default 0 " +		
		") " +
		"ENGINE=INNODB; ";
		mDB.executeUpdate(sql);
		

		sql = "" +
		"create table ASM_scontig " +
		"(  " +
		"	SCID bigint PRIMARY KEY AUTO_INCREMENT, " +
		"	AID integer NOT NULL, " +
		"	CTID1 bigint not null, " +
		"	CTID2 bigint default 0, " +
		"	TCID integer not null, " +
		"	merged_to integer default 0, " +
		"	capbury_done tinyint default 0,	 " +	
		"	UNIQUE INDEX unq1(CTID1,CTID2), " +
		"	INDEX idx1(TCID) " +
		") " +
		"ENGINE=InnoDB; ";
		mDB.executeUpdate(sql);

		sql = "" +
		"create table ASM_assemtime " +
		"( " +
		"	AID integer not null , " +
		"	stage tinytext, " +
		"	time_start datetime, " +
		"	INDEX(AID) " +
		") " +
		"ENGINE=MyISAM;";
		mDB.executeUpdate(sql);
		
		sql = "" +
		"create table ASM_params " +
		"( " +
		"	AID integer not null, " +
		"	pname tinytext, " +
		"	pvalue text	 " +	
		") " +
		"ENGINE=MyISAM; ";
		mDB.executeUpdate(sql);
		
		sql = "" +
		"create table ASM_cmdlist " +
		"( " +
		"	AID integer not null, " +
		"	descr VARCHAR(100), " +
		"	cmdstr text, " +
		"	UNIQUE INDEX unq(AID,descr) " +
		") " +
		"ENGINE=MyISAM; ";
		mDB.executeUpdate(sql);
		
		sql = "" +
		"create table snp " +
		"( " +
		"	SNPID bigint not null PRIMARY KEY AUTO_INCREMENT, " +
		"	CTGID bigint not null, " +
		"	pos integer not null, " +
		"	basevars tinytext, " +
		"	annot text, " +
		"	numvars smallint, " +
		"	snptype tinytext,	 " +	
		"	score float default 0,	 " +	
		"	INDEX(CTGID) " +
		") " +
		"ENGINE=MyISAM; ";
		mDB.executeUpdate(sql);
		
		sql = "" +
		"create table snp_clone " +
		"( " +
		"	SNPID bigint not null, " +
		"	CID bigint not null, " +
		"	snptype tinytext,		 " +
		"	UNIQUE INDEX(SNPID, CID), " +
		"	INDEX(CID)		 " +
		") " +
		"ENGINE=MyISAM; ";
		mDB.executeUpdate(sql);
		
		sql = "" +
		"create table assem_msg " +
		"	( " +
		"	AID integer NOT NULL PRIMARY KEY, " +
		"	msg text " +
		"	) " +
		"	ENGINE=MyISAM; ";
		mDB.executeUpdate(sql);
		
		sql = "" +
		"create table schemver " +
		"( " +
		"	schemver tinytext, " +
		"	annoVer  tinytext, " +
		"   annoDate  tinytext " + // not system date, but Version.strRelDate
		")  " +
		"ENGINE=MyISAM; ";
		mDB.executeUpdate(sql);
		
		Vector<Integer> idlist = new Vector<Integer>();
		mDB.executeQuery("lock tables assembly write, ASM_tc_iter write");
		ResultSet rs = mDB.executeQuery("select aid from assembly");
		while (rs.next())
		{
			int aid = rs.getInt("aid");
			idlist.add(aid);
		}
		for (int aid : idlist)
		{
			mDB.executeUpdate("insert into ASM_tc_iter (aid,tctype,tcnum,finished) values(" + aid + ",'Final',1,1)");			
		}

		Utils.singleLineMsg("library table...");
		mDB.executeQuery("lock tables library write");
		mDB.executeUpdate("alter table library add avglen int default 0");
		mDB.executeQuery("lock tables library write");
		mDB.executeUpdate("alter table library add medlen int default 0");
		mDB.executeQuery("lock tables library write");
		mDB.executeUpdate("alter table library add defqual tinytext");
		mDB.executeQuery("lock tables library write");
		mDB.executeUpdate("alter table library add ctglib boolean default 0");
		
		Utils.singleLineMsg("clone table...");
		mDB.executeQuery("lock tables clone write");
		mDB.executeUpdate("alter table clone add mate_CID bigint not null default 0");
		mDB.executeQuery("lock tables clone write");
		mDB.executeUpdate("alter table clone add LID integer not null default 0");
		mDB.executeQuery("lock tables clone write");
		mDB.executeUpdate("alter table clone add chimeric boolean not null default 0");
		mDB.executeQuery("lock tables clone write");
		mDB.executeUpdate("alter table clone add index lid1(LID,cloneid)");
		mDB.executeQuery("lock tables clone write");
		mDB.executeUpdate("alter table clone add index lid2(LID,CID)");
		mDB.executeQuery("lock tables clone write");
		mDB.executeUpdate("alter table clone modify column sequence mediumtext not null");
		mDB.executeQuery("lock tables clone write");
		mDB.executeUpdate("alter table clone modify column quality mediumtext not null");
		mDB.executeQuery("lock tables clone write, library write");
		mDB.executeUpdate("update clone,library set clone.lid=library.lid where library.libid=clone.libid");

		Utils.singleLineMsg("assembly table...");
		mDB.executeQuery("lock tables assembly write");
		mDB.executeUpdate("alter table assembly add completed tinyint default 0");
		mDB.executeQuery("lock tables assembly write");
		mDB.executeUpdate("alter table assembly add completedate date");
		mDB.executeQuery("lock tables assembly write");
		mDB.executeUpdate("alter table assembly add erate float default .005");
		mDB.executeQuery("lock tables assembly write");
		mDB.executeUpdate("alter table assembly add exrate float default .005");
		
		Utils.singleLineMsg("assemlib table...");
		mDB.executeQuery("lock tables assemlib write");
		mDB.executeUpdate("alter table assemlib add LID integer NOT NULL default 0");
		mDB.executeQuery("lock tables assemlib write");
		mDB.executeUpdate("alter table assemlib add AID integer NOT NULL default 0");
		mDB.executeQuery("lock tables assemlib write, assembly write, library write");
		mDB.executeUpdate("update assemlib, assembly set assemlib.aid=assembly.aid where assembly.assemblyid=assemlib.assemblyid");
		mDB.executeUpdate("update assemlib,library set assemlib.lid=library.lid where library.libid=assemlib.libid");
				
		Utils.singleLineMsg("contig table...");
		mDB.executeQuery("lock tables contig write");
		mDB.executeUpdate("alter table contig add AID integer NOT NULL default 0");
		mDB.executeQuery("lock tables contig write");
		mDB.executeUpdate("alter table contig add TCID integer NOT NULL default 1");
		mDB.executeQuery("lock tables contig write");
		mDB.executeUpdate("alter table contig add SCTGID bigint NOT NULL default 0");
		mDB.executeQuery("lock tables contig write");
		mDB.executeUpdate("alter table contig add allele_count smallint default 0");		
		mDB.executeQuery("lock tables contig write");
		mDB.executeUpdate("alter table contig add orig_ccs mediumtext");

		mDB.executeQuery("lock tables contig write");
		mDB.executeUpdate("alter table contig add indel_count integer default 0");
		mDB.executeQuery("lock tables contig write");
		mDB.executeUpdate("alter table contig add mate_CTGID bigint default 0");

		mDB.executeQuery("lock tables contig write");
		mDB.executeUpdate("alter table contig add buried_placed boolean default 0");
		mDB.executeQuery("lock tables contig write");
		mDB.executeUpdate("alter table contig add avg_pct_aligned smallint default 0");
		mDB.executeQuery("lock tables contig write");
		mDB.executeUpdate("alter table contig add finalized boolean default 0");

		mDB.executeQuery("lock tables contig write");
		mDB.executeUpdate("alter table contig add index idx4(TCID)");
		mDB.executeQuery("lock tables contig write");
		mDB.executeUpdate("alter table contig add index idx5(SCTGID)");

		mDB.executeQuery("lock tables contig write");
		mDB.executeUpdate("alter table contig modify column consensus mediumtext not null");		
		mDB.executeQuery("lock tables contig write");
		mDB.executeUpdate("alter table contig modify column quality mediumtext not null");		
		mDB.executeQuery("lock tables contig write");
		mDB.executeUpdate("update contig set orig_ccs = consensus");
		mDB.executeQuery("lock tables contig write, ASM_tc_iter write, assembly write");
		mDB.executeUpdate("update contig, assembly set contig.aid=assembly.aid where assembly.assemblyid=contig.assemblyid");
		mDB.executeUpdate("update contig, ASM_tc_iter set contig.tcid=ASM_tc_iter.tcid where ASM_tc_iter.aid=contig.aid");

		Utils.singleLineMsg("contclone table...");
		mDB.executeQuery("lock tables contclone write");
		mDB.executeUpdate("alter table contclone add CTGID bigint NOT NULL default 0");
		mDB.executeQuery("lock tables contclone write");
		mDB.executeUpdate("alter table contclone add CID bigint NOT NULL default 0");
		mDB.executeQuery("lock tables contclone write");
		mDB.executeUpdate("alter table contclone modify gaps mediumtext");
		mDB.executeQuery("lock tables contclone write");
		mDB.executeUpdate("alter table contclone add extras mediumtext");
		mDB.executeQuery("lock tables contclone write");
		mDB.executeUpdate("alter table contclone add ngaps integer default 0");
		mDB.executeQuery("lock tables contclone write");
		mDB.executeUpdate("alter table contclone add pct_aligned smallint default 0");

		mDB.executeQuery("lock tables contclone write");
		mDB.executeQuery("lock tables clone write,contig write, contclone write");
		mDB.executeUpdate("update contclone,contig set contclone.ctgid=contig.ctgid where contig.contigid=contclone.contigid");
		mDB.executeUpdate("update contclone,clone set contclone.cid=clone.cid where clone.cloneid=contclone.cloneid");
		mDB.executeQuery("lock tables contclone write");
		mDB.executeUpdate("alter table contclone add unique index(CTGID,CID)");
		
		Utils.singleLineMsg("buryclone table..."); // These must be in same order as when created from scratch
		mDB.executeQuery("lock tables buryclone write");
		mDB.executeUpdate("alter table buryclone add CID_child bigint NOT NULL default 0");
		mDB.executeQuery("lock tables buryclone write");
		mDB.executeUpdate("alter table buryclone add CID_parent bigint NOT NULL default 0");
		mDB.executeQuery("lock tables buryclone write");
		mDB.executeUpdate("alter table buryclone add CID_topparent bigint NOT NULL default 0");
		mDB.executeQuery("lock tables buryclone write");
		mDB.executeUpdate("alter table buryclone add AID integer NOT NULL default 0");
		mDB.executeQuery("lock tables buryclone write");
		mDB.executeUpdate("alter table buryclone add bcode tinytext");
		mDB.executeQuery("lock tables buryclone write");
		mDB.executeUpdate("alter table buryclone add flipped tinyint default 0");
		mDB.executeQuery("lock tables buryclone write");
		mDB.executeUpdate("alter table buryclone add TCID integer default 0");
		mDB.executeQuery("lock tables  buryclone write");
		mDB.executeUpdate("alter table buryclone add index (CID_child)");
		mDB.executeQuery("lock tables buryclone write");
		mDB.executeUpdate("alter table buryclone add index (CID_parent)");
		mDB.executeQuery("lock tables buryclone write");
		mDB.executeUpdate("alter table buryclone add index (CID_topparent)");
		mDB.executeQuery("lock tables buryclone write, clone write, assembly write");
		mDB.executeUpdate("update buryclone,assembly set buryclone.aid=assembly.aid where buryclone.assemblyid=assembly.assemblyid");
		mDB.executeUpdate("update buryclone,clone set buryclone.cid_child=clone.cid where clone.cloneid=buryclone.childid");
		mDB.executeUpdate("update buryclone,clone set buryclone.cid_parent=clone.cid where clone.cloneid=buryclone.parentid");
		mDB.executeQuery("lock tables buryclone write");
		mDB.executeUpdate("alter table buryclone add unique index(AID,CID_child)");

		// Fill the ASM_scontig table
		mDB.executeQuery("lock tables ASM_scontig write, contig write");
		mDB.executeUpdate("insert into ASM_scontig (select 0,aid,ctgid,0,tcid,0,0 from contig)");
		mDB.executeUpdate("update ASM_scontig set merged_to=scid");
		mDB.executeUpdate("update contig,ASM_scontig set contig.sctgid=ASM_scontig.scid where contig.ctgid=ASM_scontig.ctid1");

		updateFrom20to22(); // a few extra fields to add
		
		// Get the top parents set right
		mDB.executeQuery("lock tables buryclone write, buryclone as bc1 write, buryclone as bc2 write");
		mDB.executeUpdate("update buryclone set cid_topparent = cid_parent");
		while (true)
		{
			int nRows = mDB.executeUpdate("update buryclone as bc1, buryclone as bc2 set bc1.CID_topparent=bc2.CID_topparent " +
						"where  bc1.CID_parent=bc2.CID_child and bc1.CID_topparent != bc2.CID_topparent and bc1.aid= bc2.aid");
			if (nRows == 0)
			{
				break;
			}
			Log.msg("adjust " + nRows + " top parents",LogLevel.Detail);
		}

		mDB.executeQuery("lock tables assembly write, schemver write");
		mDB.executeUpdate("update assembly set completed=1,completedate=assemblydate");
		mDB.executeUpdate("insert into schemver (schemver) values('2.2')");
		mDB.executeQuery("lock tables contclone write");
		mDB.executeUpdate("update contclone set extras=''");
		
		mDB.unlockTables();
		mDB.foreignKeysOn();
	}
	/* 
	 * Update Annotation schema
	 * v2.5 did not support older annotation
	 * v3.0 does not support old pairwise but does support old DB hits
	*/
	public void updateSchemaAnnotate () throws Exception
	{
		String sql;
		
		// pre-v2.5 
		if (!mDB.tableHasColumn("contig", "PID")) mDB.executeUpdate("alter table contig ADD PID bigint");
		if (mDB.hasTable("pairwise_contigs")) mDB.executeUpdate("drop table pairwise_contigs");
		
		// the v2.5 pja_pairwise did not have PWID, which will be need for future work
		if (mDB.hasTable("pja_pairwise") && !mDB.tableHasColumn("pja_pairwise", "AID"))
			mDB.executeUpdate("drop table pja_pairwise"); 
		
		if (mDB.hasTable("pja_uniprot_hits")) // renamed for v3.0
			mDB.executeUpdate("rename table pja_uniprot_hits to pja_db_unitrans_hits");
		
		if (mDB.hasTable("assem_msg") && !mDB.tableHasColumn("assem_msg", "pja_msg"))
			mDB.executeUpdate("alter table assem_msg add pja_msg text default null"); 
		
		// add all pja tables that do not exist
		loadSchemaAnnotate(); 
		
		// v3.0 columns added to existing tables 
		if (!mDB.tableHasColumn("contig", "PIDov")) 
			mDB.executeUpdate("alter table contig ADD PIDov bigint");
		
		if (!mDB.tableHasColumn("pja_db_unitrans_hits", "AID"))
		{
			sql = "ALTER TABLE pja_db_unitrans_hits " +
		 	  " ADD AID 			bigint, " +
			  " ADD DUHID 		bigint, " +
			  " ADD taxonomy  	VARCHAR(20), " +
			  " ADD filtered  	smallint  unsigned  default 0, " +
			  " ADD filter_best		boolean default 0, " +   // best Eval
			  " ADD filter_ovbest	boolean default 0, " +   // best Anno
			  " ADD filter_olap		boolean default 0, " +
			  " ADD filter_top3		boolean default 0, " +
			  " ADD filter_species	boolean default 0, " +
			  " ADD filter_gene		boolean default 0, " +
			  " ADD cnt_hsps			smallint unsigned default 0, " +
			  " ADD ctg_merge_start 	smallint default 0," +
			  " ADD ctg_merge_end	smallint default 0," +
			  " ADD hit_merge_start  smallint default 0," +
			  " ADD hit_merge_end 	smallint default 0, " +
			  " ADD isProtein boolean default false, " +  // really replaces complement
			  " DROP complement, " +
			  " DROP assembly_id," +
			  " " +
			  " ADD INDEX(filter_best), " +
			  " ADD INDEX(filter_ovbest), " +
			  " ADD INDEX(filter_olap), " +
			  " ADD INDEX(filter_top3), " +
			  " ADD INDEX(filter_species), " +
			  " ADD INDEX(filter_gene), " +
			  "add    INDEX(uniprot_id), " +
			  "add  INDEX(contigid), " +
			  "add	 INDEX(CTGID) "; 
			mDB.executeUpdate(sql);
		}

		if (!mDB.tableHasColumn("contig", "dbtype"))
		{	sql = "ALTER TABLE contig " +
			"   ADD description   VARCHAR(250), " + 
			"   ADD species       VARCHAR(100), " +
			"   ADD dbtype        VARCHAR(10)," + 
			"   ADD taxonomy      VARCHAR(30)," + 
			"   ADD cnt_swiss	  smallint  unsigned  default 0, " +
			"   ADD cnt_trembl    smallint  unsigned default 0, " +
			"   ADD cnt_nt        smallint  unsigned default 0, " +
			"   ADD cnt_overlap   SMALLINT  unsigned default 0, " +
			"   ADD cnt_species   SMALLINT  unsigned default 0, " +
			"   ADD cnt_gene	      SMALLINT  unsigned default 0, " +
			"   ADD cnt_taxo	      SMALLINT  unsigned default 0, " +
			"   ADD cnt_pairwise  smallint  unsigned  default 0," +
			"	ADD INDEX idx6(AID,bestmatchid) "; 
			mDB.executeUpdate(sql);
		}
	}
	
	/**********************************************************************
	 * 		XXX Assembly stuff
	 **********************************************************************/
	public void dropInnoDBTables() throws Exception
	{
		try
		{
			mDB.executeUpdate("drop table ASM_clique_clone");
			mDB.executeUpdate("drop table ASM_cliques");
			mDB.executeUpdate("drop table ASM_tc_edge");
			mDB.executeUpdate("drop table ASM_scontig");
			mDB.executeUpdate("drop table ASM_tc_iter");
		}
		catch(Exception e)
		{
			// If it's an upgrade we might not be able to drop them due to foreign keys - no big deal
		}
	}
	// Since we clean up the INNODB tables, this is needed to add them back if
	// an assembly is re-done.
	public void addASMTables() throws Exception
	{
		String sql;
		if (!mDB.hasTable("ASM_tc_iter"))
		{
			sql = "" +
			"create table ASM_tc_iter " +
			"( " +
			"	TCID integer PRIMARY KEY AUTO_INCREMENT, " +
			"	AID integer NOT NULL, " +
			"	tctype tinytext NOT NULL, " +
			"	tcnum integer, " +
			"	finished tinyint default 0, " +
			"	clustiter_done smallint default 0, " +
			"	ctgs_start integer default 0, " +
			"	ctgs_end integer default 0, " +
			"	merges_tried integer default 0, " +
			"	merges_ok integer default 0 " +
			" " +
			") " +
			"ENGINE=INNODB; " ;
			mDB.executeUpdate(sql);
		}
		if (!mDB.hasTable("ASM_cliques"))
		{

			sql = "" +
			"create table ASM_cliques " +
			"( " +
			"	AID integer NOT NULL, " +
			"	CQID bigint PRIMARY KEY AUTO_INCREMENT, " +
			"	type tinytext, " +
			"	nclone integer default 0, " +
			"	assembled tinyint default 0, " +
			"	cap_success tinyint default 0 " +
			" " +
			") " +
			"ENGINE=INNODB; ";
			mDB.executeUpdate(sql);
		}
		if (!mDB.hasTable("ASM_clique_clone"))
		{
		
			sql = "" +
			"create table ASM_clique_clone " +
			"( " +
			"	CQID bigint, " +
			"	CID bigint, " +
			"	PRIMARY KEY(CQID,CID), " +
			" " +
			"	FOREIGN KEY (CQID) REFERENCES ASM_cliques(CQID) ON DELETE CASCADE  " +
			") " +
			"ENGINE=INNODB; ";
			mDB.executeUpdate(sql);
		}
		/* ASM_scontig stands for "supercontig" 
		it holds either one or two actual assembled contigs 
		purpose is to track contig pairs through the assembly 
		it is these which are merged during the TC rounds  */
		if (!mDB.hasTable("ASM_scontig"))
		{

			sql = "" +
			"create table ASM_scontig " +
			"( " +
			"	SCID bigint PRIMARY KEY AUTO_INCREMENT, " +
			"	AID integer NOT NULL, " +
			"	CTID1 bigint not null, " +
			"	CTID2 bigint default 0, " +
			"	TCID integer not null, " +
			"	merged_to integer default 0, " +
			"	capbury_done tinyint default 0, " +
			" " +
			"	UNIQUE INDEX unq1(CTID1,CTID2), " +
			"	INDEX idx1(TCID), " +
			"	FOREIGN KEY (TCID) REFERENCES ASM_tc_iter(TCID) ON DELETE CASCADE " +
			") " +
			"ENGINE=INNODB; ";
			mDB.executeUpdate(sql);
		}
		if (!mDB.hasTable("ASM_tc_edge"))
		{

			sql = "" +
			"create table ASM_tc_edge " +
			"( " +
			"	EID bigint PRIMARY KEY AUTO_INCREMENT, " +
			"	TCID integer, " +
			"	SCID1 bigint, " +
			"	SCID2 bigint, " +
			"	score integer, " +
			"	CLUSTID bigint default 0, " +
			"	attempted tinyint default 0, " +
			"	succeeded tinyint default 0, " +
			"	errstr tinytext, " +
			"	errinfo tinytext, " +
			"	SCID_result bigint default 0, " +
			"	EID_from bigint default 0, " +
			"	olap smallint default 0, " +
			"	identity smallint default 0, " +
			"	hang smallint default 0, " +
			" " +
			"	UNIQUE INDEX unq1(TCID,SCID1,SCID2), " +
			"	INDEX scodx(TCID,score), " +
			"	INDEX att(TCID,attempted), " +
			"	 FOREIGN KEY (TCID) REFERENCES ASM_tc_iter(TCID) ON DELETE CASCADE , " +
			"	 FOREIGN KEY (SCID1) REFERENCES ASM_scontig(SCID) ON DELETE CASCADE , " +
			"	 FOREIGN KEY (SCID2) REFERENCES ASM_scontig(SCID) ON DELETE CASCADE  " +
			") " +
			"ENGINE=INNODB; ";
			mDB.executeUpdate(sql);
		}
	}
	
	public static void addCtgLibFields(DBConn db) throws Exception
	{
		Vector<String> cols = new Vector<String>();
		TreeMap<String,Integer> lib2size = new TreeMap<String,Integer>();
		TreeMap<Integer,Integer> assem2MaxSize = new TreeMap<Integer,Integer>();
		ResultSet rs;
		// First, get the library sizes, and the max lib size for each assembly,
		// which we need for calculating the parts-per-N number
		rs = db.executeQuery("select library.libid, libsize, aid from library " + 
				" join assemlib on assemlib.lid=library.lid");
		while (rs.next())
		{
			String libid = rs.getString("libid");
			int size = rs.getInt("libsize");
			int aid = rs.getInt("aid");
			lib2size.put(libid, size);
			if (!assem2MaxSize.containsKey(aid))
			{
				assem2MaxSize.put(aid,	0);
			}
			if (size > assem2MaxSize.get(aid)) 
			{
				assem2MaxSize.put(aid, size);
			}
		}
		for (int aid : assem2MaxSize.keySet())
		{
			//int ppxN = (int)(Math.ceil(Math.log10(assem2MaxSize.get(aid))));
			if (!db.tableHasColumn("assembly", "ppx"))
			{
				db.executeUpdate("alter table assembly add ppx tinyint default 0");	
			}
			// Not needed now for RPKM (could probably eliminate more code, too)
			//db.executeUpdate("update assembly set ppx=" + ppxN + " where aid=" + aid);
		}
		
		int ncols = lib2size.keySet().size();
		for (String col : lib2size.keySet())
		{
			//Utils.singleLineMsg("Adding " + ncols + " expression count columns");
			Thread.sleep(1000);
			String column = "L__" + col;
			if (!db.tableHasColumn("contig", column))
			{
				db.executeUpdate("alter table contig add " + column + " bigint unsigned default 0");
			}
			column = "LN__" + col;
			if (!db.tableHasColumn("contig", column))
			{
				db.executeUpdate("alter table contig add " + column + " float default 0.0");
			}			

			if (!db.tableHasColumn("contig", "totalexp"))
			{
				db.executeUpdate("alter table contig add totalexp bigint unsigned default 0");
				db.executeUpdate("alter table contig add totalexpN bigint unsigned default 0");
			}
			ncols--;
		}
		
	}	
	// Fill the contig table lib count fields, on an upgrade.
	// Assumes that contig_counts is correct.
	private void fillCtgLibFields(DBConn db) throws Exception
	{
		// Transfer the counts to the contig table
		// First, need library names .
		ResultSet rs = db.executeQuery("select libid, ctglib from library ");
		TreeSet<String> libs = new TreeSet<String>();
		TreeSet<String> ctgLibs = new TreeSet<String>();
		while (rs.next())
		{
			String libid = rs.getString("libid");
			boolean ctglib = rs.getBoolean("ctglib");
			libs.add(libid);
			if (ctglib) ctgLibs.add(libid);
		}
		rs.close();

		Vector<String> Ls = new Vector<String>();
		Vector<String> LNs = new Vector<String>();

		for (String libid : libs)
		{
			String col = "L__" + libid;
			String colN = "LN__" + libid;
			db.executeUpdate("update contig, contig_counts as cc set contig." + col + "=cc.count " + 
					" where cc.libid='" + libid + "' and cc.contigid=contig.contigid");
			Utils.setRPKM(db, libid);
			if (ctgLibs.contains(libid)) continue;
			Ls.add("L__" + libid);
			LNs.add("LN__" + libid);
			
		}
		if (Ls.size() > 0)
		{
			String LSum = Utils.join(Ls,"+");
			String LNSum = Utils.join(LNs,"+");
			db.executeUpdate("update contig set totalexp=(" +LSum + "), totalexpN=(" + LNSum + ") ");
		}
	}
	
}
